package com.kool.leitu.base.excel;

import com.kool.leitu.base.bean.ColumnBean;
import com.kool.leitu.base.bean.TableBean;
import com.kool.leitu.base.exception.AppException;
import com.kool.leitu.base.util.DateUtils;
import com.kool.leitu.base.util.FileUtils;
import com.kool.leitu.base.util.StringUtils;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringWriter;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: tianhe
 * @create: 2019/02/01 17:19
 */
@Component
public class ExcelToSqlTransfer {

    /**
     * @param listSheet
     * @param templatePath
     * @param toDir
     */
    public String generate(List<List<List<String>>> listSheet, String templatePath, String toDir) {
        StringBuffer sbSql = new StringBuffer();
        for (List<List<String>> sheet : listSheet) {
            TableBean table = sheetValueToTableBean(sheet);
            String sql = tableBeanToSql(table, templatePath);
            sbSql.append(sql);
            sbSql.append("\r\n\r\n");
        }
        if (!StringUtils.isEmpty(toDir)) {
            FileUtils.writeFile(sbSql.toString(), toDir, "UTF-8", false);
        }
        return sbSql.toString();
    }

    /**
     * @param tableBean
     * @param templatePath
     * @return
     */
    private String tableBeanToSql(TableBean tableBean, String templatePath) {
        Configuration cfg = new Configuration(Configuration.DEFAULT_INCOMPATIBLE_IMPROVEMENTS);
        cfg.setDefaultEncoding("UTF-8");
        String sql = null;
        try {
            cfg.setDirectoryForTemplateLoading(new File(templatePath));
            Template t = cfg.getTemplate("table.ftl");
            StringWriter writer = new StringWriter();
            t.process(tableBean, writer);
            sql = writer.toString();
        } catch (IOException e) {
            throw new AppException(1000001, e, "freemarker转sql错误");
        } catch (TemplateException e) {
            throw new AppException(1000001, e, "freemarker转sql错误");
        }
        return sql;
    }

    /**
     * @param list
     * @return
     */
    private TableBean sheetValueToTableBean(List<List<String>> list) {
        TableBean tableBean = new TableBean();
        String createTime = DateUtils.getCurrentTimeString(DateUtils.FORMAT_TIMESTAMP);
        String tableId = list.get(0).get(1);
        String tableName = list.get(0).get(2);
        tableBean.setCreateTime(createTime);
        tableBean.setTableId(tableId);
        tableBean.setTableName(tableName);

        ArrayList<ColumnBean> columns = new ArrayList<>();
        for (int i = 2; i < list.size(); i++) {
            List<String> listCell = list.get(i);
            if (listCell.get(0) == null || listCell.get(0).equals("")) {
                continue;
            }
            String colId = listCell.get(0);
            String colName = listCell.get(1);
            String colType = listCell.get(2);
            String colComment = listCell.get(3);

            ColumnBean column = new ColumnBean();
            column.setId(colId);
            column.setName(colName);
            column.setType(colType);
            column.setComment(colComment.replaceAll("\n",""));
            column.setBinary(isString(colType));
            if (isString(colType)) {
                column.setDefaultValue("''");
            } else if (isDatetime(colType)) {
                column.setDefaultValue("CURRENT_TIMESTAMP");
            } else if (isDate(colType)) {
                column.setDefaultValue("'1900-01-01'");
            } else if (isDigit(colType)) {
                column.setDefaultValue("0");
            }else{
                column.setDefaultValue("''");
            }

            columns.add(column);
        }
        tableBean.setColumns(columns);

        return tableBean;
    }

    /**
     * @param colType
     * @return
     */
    private boolean isString(String colType) {
        colType = colType.toLowerCase();
        if (colType.indexOf("char") >= 0 || colType.indexOf("varchar") >= 0 || colType.indexOf("text") >= 0) {
            return true;
        }
        return false;
    }

    private boolean isDatetime(String colType) {
        colType = colType.toLowerCase();
        if (colType.indexOf("datetime") >= 0 || colType.indexOf("timestamp") >= 0) {
            return true;
        }
        return false;
    }

    private boolean isDate(String colType) {
        colType = colType.toLowerCase();
        if (colType.indexOf("date") >= 0) {
            return true;
        }
        return false;
    }

    private boolean isDigit(String colType) {
        colType = colType.toLowerCase();
        if (colType.indexOf("int") >= 0 || colType.indexOf("bigint") >= 0 ) {
            return true;
        }
        return false;
    }
}
